# -*- coding: UTF-8 -*-

exclude_tables = [
"o_bas_order_coupon_d"
,"o_bas_order_detail_d"
,"o_bas_order_info_d"
,"o_bas_order_status_log_d"
,"o_bas_order_trade_d"
,"o_bas_return_order_detail"
,"o_bas_return_order_info"
,"o_bas_order_fail_log_d"
,"o_bas_scan_d"
,"o_bas_visit_d"
,"o_bas_visit_tender_d"
,"o_bas_item_ascii"
]

with open("D:\\data-assets\\aloha_ods.tsv",'r',encoding='UTF-8') as f :
      content = f.read()
      content_array = content.split("cn_ods_aloha")

cn = content_array[1]
index_start=500
base_col_index=300000

def gen_table_insert( table_content , table_index , col_index  ):
    fields = []
    col_array = []
    rs = []
    for index in range(len(cn.split("\n"))):
        lines = cn.split("\n")
        if index == 0 :
            table_name = "cn_ods_aloha" + lines[index].replace(" " , '')
        elif index >= 4 and len(lines[index]):
            lines[index].split(" ")
            fields.append( list(filter( lambda x: x != "", lines[index].split(" "))))

    insert_sql = "INSERT INTO  meta_data_table(code,name,name_en,theme_type,data_layer,type,effect,maintainer,particle,tb_column,relation_tb,sences,status,create_time,update_time,goal,is_partition,upd_logic,in_out_table,save_format,db_name,is_del)"
    sub_insert_sql = "VALUES('B"+str(index_start + table_index)+"','"+table_name+"','"+table_name+"' , 1 , 5 , 1 , 0 , '王义飞' , '商品' , '_cols_' , '' , 1 , 0 ,getdate() , getdate() , '存储 ods 层数据' , 0 , 0 , 0 , 'ORC' ,'"+table_name.split(".")[0]+"' , 1 );";
    insert_sql = insert_sql + " " + sub_insert_sql
    meta_col_insert_sql = "insert into meta_col(code,name,name_en,describe,type,is_primary,is_null,create_time,update_time)values("
    for ss in fields:
        rs.append( meta_col_insert_sql + " 'ZD"+str(base_col_index + col_index )+"','"+ss[0]+"','"+ss[0]+"' , '"+ ("" if len(ss) > 1 else ss[2] )+"' , '"+ss[1]+"' , 0 , 1 , getdate() , getdate() );")
        col_array.append("ZD"+str(base_col_index + col_index ))
        col_index = col_index+1

    rs.append(insert_sql.replace("_cols_" , ",".join(col_array)))
    rs.append(table_index)
    rs.append(col_index)
    return rs

for index in range(len(content_array)):
    table_content = content_array[index]
    ss = index
    cont = gen_table_insert(table_content , ss, ss)
    index_start = index_start + int(cont[-2:][0])
    base_col_index = base_col_index + int(cont[-2:][1])
    if len(list(filter( lambda x : table_content.find(x) >= 0 , exclude_tables ))) == 0 :
        with open("D:\\data-assets\\rs.sql" , 'a+',encoding='UTF-8') as f :
            f.write("\n" + "\n".join(cont[:-2]))



